﻿Imports KOR.Helpers
Imports KOR.Helpers.Data
Imports KOR.Support

Namespace BL

    Public Module InventoryAdjustments

#Region "Colors_DML"

        Public Function InsertInventoryAdjustments(dict As Dictionary(Of String, Object)) As Integer
            Dim retVal As Integer = DDA.Insert("InventoryAdjustmentSessions", dict)
            retVal = InsertProductsForInventoryAdjustment(retVal)
            Return retVal
        End Function

        Public Function UpdateInventoryAdjustments(dict As Dictionary(Of String, Object), InventorySessionID As Integer) As Integer
            Return DDA.Update("InventoryAdjustmentSessions", dict, "InventorySessionID=" & InventorySessionID)
        End Function

        Public Function DeleteColor(ByVal InventorySessionID As Integer) As Integer
            Return DDA.Delete("InventoryAdjustmentSessions", "ColorID=" & InventorySessionID, False)
        End Function

        Public Function InsertProductsForInventoryAdjustment(InventoryAdjustmentID As Integer) As Integer
            Dim sql As String = "INSERT INTO InventoryAdjustmentDetails(InventorySessionID,ProductID,OriginalQuantity) " & _
                                " SELECT " & InventoryAdjustmentID & ", Products.ProductID,Inventory.Quantity FROM Products LEFT JOIN Inventory ON Products.ProductID = Inventory.ProductID"

            Return DDA.ExecuteNonQuery(sql)
        End Function

#End Region

#Region "Colors_Selection"

        Public Function GetAllInventorySessions() As IDataReader
            Dim sql As String = "SELECT * FROM InventoryAdjustmentSessions Order By InventorySessionID DESC"
            Return DDA.SelectData(sql)
        End Function

        Public Function GetAllInventorySessions(searchFor As String, criteria As String, startRowIndex As Integer, maximumRows As Integer, SortBy As String) As IDataReader
            Dim sql As String
            sql = "SELECT * FROM InventoryAdjustmentSessions "

            If SortBy = "" Then SortBy = " CreatedDate Desc"

            Return DDA.SelectDataPaginated(sql, startRowIndex, maximumRows, SortBy)
        End Function

        Public Function GetAllInventorySessionCount(searchFor As String, criteria As String) As Integer
            Dim sql As String
            sql = "SELECT * FROM InventoryAdjustmentSessions "
            Return DDA.SelectDataCount(sql)
        End Function

#End Region

#Region "Inventory_Adjustment_Detail"

        Public Function GetInventoryAdjustmentDetails(searchFor As String, criteria As String, startRowIndex As Integer, maximumRows As Integer, SortBy As String) As IDataReader

            Dim sql As String = "SELECT InventoryAdjustmentDetails.*, Products.ProductName FROM Products LEFT JOIN InventoryAdjustmentDetails ON Products.ProductID = InventoryAdjustmentDetails.ProductID "

            If searchFor <> "" Then
                sql = sql & " WHERE (Products.ProductName LIKE '%" & searchFor & "%') "
            End If
            If criteria <> "" Then
                If Not sql.Contains("WHERE") Then sql = sql & " WHERE " & criteria Else sql = sql & " AND " & criteria
            End If
            If SortBy = "" Then SortBy = " ProductName ASC"
            Return DDA.SelectDataPaginated(sql, startRowIndex, maximumRows, SortBy)
        End Function

        Public Function GetInventoryAdjustmentDetailsCount(searchFor As String, criteria As String) As Integer

            Dim sql As String = "SELECT InventoryAdjustmentDetails.*, Products.ProductName FROM Products LEFT JOIN InventoryAdjustmentDetails ON Products.ProductID = InventoryAdjustmentDetails.ProductID "
            If searchFor <> "" Then
                sql = sql & " WHERE (Products.ProductName LIKE '%" & searchFor & "%') "
            End If
            If criteria <> "" Then
                If Not sql.Contains("WHERE") Then sql = sql & " WHERE " & criteria Else sql = sql & " AND " & criteria
            End If
            Return DDA.SelectDataCount(sql)
        End Function

        Public Function InsertInventoryAdjustmentDetails(dict As Dictionary(Of String, Object)) As Integer
            Return DDA.Insert("InventoryAdjustmentDetails", dict)
        End Function

        Public Function UpdateInventoryAdjustmentDetail(dict As Dictionary(Of String, Object), InventorySessionID As Integer) As Integer
            Return DDA.Update("InventoryAdjustmentDetails", dict, "InventorySessionID=" & InventorySessionID)
        End Function

        Public Function DeleteInventoryAdjustmentDetail(ByVal DetailID As Integer) As Integer
            Return DDA.Delete("InventoryAdjustmentDetails", "DetailID=" & DetailID, False)
        End Function


        Public Function UpdateInventoryAfterProcessing(InventorySessionID As Integer) As Integer

            Dim sql As String = " UPDATE Inventory set Inventory.Quantity = InventoryAdjustmentDetails.CountedQuantity, " & _
                                " Inventory.LastUpdatedDate = InventoryAdjustmentDetails.PostedDate " & _
                                " FROM Inventory, InventoryAdjustmentDetails WHERE InventoryAdjustmentDetails.ProductID = Inventory.ProductID " & _
                                " AND InventoryAdjustmentDetails.IsPosted = 1 AND InventoryAdjustmentDetails.InventorySessionID = " & InventorySessionID

            Dim retVal As Integer = DDA.ExecuteNonQuery(sql)

            Return retVal
        End Function

#End Region

    End Module

End Namespace